﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using QL_DTTN.DAL;
namespace QL_DTTN.DTO.DTO
{
    public partial class MonThi
    {
        public bool Them(MonThi mon, List<ChiTietMonThi> lstChiTiet)
        {
            string strNew = " insert into " + _tableName + " (MaKyThi,MaMon,ThoiGianBatDau,ThoiGianKetThuc) values (@pMaKyThi,@pMaMon,@pThoiGianBatDau,@pThoiGianKetThuc) select @@IDENTITY ";
            List<string> lstStr = new List<string>();
            SqlDataProvider dp = new SqlDataProvider();
            dp.AddParameter(new SqlParameter("@pMaKyThi", mon.MaKyThi));
            dp.AddParameter(new SqlParameter("@pMaMon", mon.MaMon));
            dp.AddParameter(new SqlParameter("@pThoiGianBatDau", mon.ThoiGianBatDau));
            dp.AddParameter(new SqlParameter("@pThoiGianKetThuc", mon.ThoiGianKetThuc));
            for (int i = 0; i < lstChiTiet.Count(); i++)
            {
                string subStr = " insert into ChiTietMonThi (MaMonThi,MaHeDaoTao) values (@pMaMonThi,@pMaHeDaoTao" + i + ") ";
                dp.AddParameter(new SqlParameter("@pMaHeDaoTao" + i, lstChiTiet[i].MaHeDaoTao));
                lstStr.Add(subStr);
            }
            return dp.RunAddNewIdentity(strNew, lstStr, "@pMaMonThi", true);
        }
        public DataTable dataTable(long maKyThi)
        {
            string sql = " select MaMonThi,Mon.TenMon from " + _tableName + " T inner join Mon on Mon.MaMon=T.MaMon  where MaKyThi=@pMaKyThi ";
            SqlDataProvider dp = new SqlDataProvider();
            dp.AddParameter(new SqlParameter("@pMaKyThi", maKyThi));
            return dp.ExDataTable(sql, true);
        }
        public DataTable dataTableMon(long maKyThi)
        {
            string sql = " select distinct Mon.MaMon,Mon.TenMon from " + _tableName + " T inner join Mon on Mon.MaMon=T.MaMon  where MaKyThi=@pMaKyThi ";
            SqlDataProvider dp = new SqlDataProvider();
            dp.AddParameter(new SqlParameter("@pMaKyThi", maKyThi));
            return dp.ExDataTable(sql, true);
        }
        public DataTable dataTable(long maKyThi,short maHeDaoTao)
        {
            string sql = " select T.MaMonThi,Mon.TenMon from " + _tableName;
            sql += " T left join ChiTietMonThi C on C.MaMonThi=T.MaMonThi ";
            sql += "  left join Mon on Mon.MaMon=T.MaMon ";
            sql+= "  where MaKyThi=@pMaKyThi ";
            sql += maHeDaoTao==0?"":" and C.MaHeDaoTao=@pMaHeDaoTao ";
            SqlDataProvider dp = new SqlDataProvider();
            dp.AddParameter(new SqlParameter("@pMaKyThi", maKyThi));
            dp.AddParameter(new SqlParameter("@pMaHeDaoTao", maHeDaoTao));
            return dp.ExDataTable(sql, true);
        }
        public List<MonThi> lstMonThi(long maKyThi)
        {
            bool kt = false;
            string sql = " select MaMonThi, MaKyThi, MaMon, ThoiGianBatDau, ThoiGianKetThuc from " + _tableName + "  where MaKyThi=@pMaKyThi ";
            SqlDataProvider dp = new SqlDataProvider();
            dp.AddParameter(new SqlParameter("@pMaKyThi", maKyThi));
            System.Data.SqlClient.SqlDataReader sqlRr = dp.ExDataReader(sql, true);
            List<MonThi> lstMonThi = new List<MonThi>();
            if (sqlRr != null)
            {
                while (sqlRr.Read())
                {
                    MonThi m = new MonThi();
                    if (!Convert.IsDBNull(sqlRr["MaMonThi"])) m.MaMonThi = Convert.ToInt64(sqlRr["MaMonThi"]);
                    if (!Convert.IsDBNull(sqlRr["MaKyThi"])) m.MaKyThi = Convert.ToInt64(sqlRr["MaKyThi"]);
                    if (!Convert.IsDBNull(sqlRr["MaMon"])) m.MaMon = Convert.ToInt16(sqlRr["MaMon"]);
                    if (!Convert.IsDBNull(sqlRr["ThoiGianBatDau"])) m.ThoiGianBatDau = Convert.ToDateTime(sqlRr["ThoiGianBatDau"]);
                    if (!Convert.IsDBNull(sqlRr["ThoiGianKetThuc"])) m.ThoiGianKetThuc = Convert.ToDateTime(sqlRr["ThoiGianKetThuc"]);
                    kt = true;
                    lstMonThi.Add(m);
                }
                dp.CloseConnection();
                if (kt) return lstMonThi;
                else return new List<MonThi>();
            }
            else
            {
                return new List<MonThi>();
            }
        }
        public List<MonThi> lstMonThi(MonThi monThi)
        {
            bool kt = false;
            string sql = " select MaMonThi, MaKyThi, MaMon, ThoiGianBatDau, ThoiGianKetThuc from " + _tableName + "  where MaKyThi=@pMaKyThi and (ThoiGianBatDau BETWEEN @pThoiGianBatDau AND @pThoiGianKetThuc or ThoiGianKetThuc BETWEEN @pThoiGianBatDau AND @pThoiGianKetThuc or @pThoiGianKetThuc BETWEEN ThoiGianBatDau AND ThoiGianKetThuc or @pThoiGianBatDau BETWEEN ThoiGianBatDau AND ThoiGianKetThuc) ";
            SqlDataProvider dp = new SqlDataProvider();
            dp.AddParameter(new SqlParameter("@pMaKyThi", monThi.MaKyThi));
            dp.AddParameter(new SqlParameter("@pThoiGianKetThuc", monThi.ThoiGianKetThuc));
            dp.AddParameter(new SqlParameter("@pThoiGianBatDau", monThi.ThoiGianBatDau));
            System.Data.SqlClient.SqlDataReader sqlRr = dp.ExDataReader(sql, true);
            List<MonThi> lstMonThi = new List<MonThi>();
            if (sqlRr != null)
            {
                while (sqlRr.Read())
                {
                    MonThi m = new MonThi();
                    if (!Convert.IsDBNull(sqlRr["MaMonThi"])) m.MaMonThi = Convert.ToInt64(sqlRr["MaMonThi"]);
                    if (!Convert.IsDBNull(sqlRr["MaKyThi"])) m.MaKyThi = Convert.ToInt64(sqlRr["MaKyThi"]);
                    if (!Convert.IsDBNull(sqlRr["MaMon"])) m.MaMon = Convert.ToInt16(sqlRr["MaMon"]);
                    if (!Convert.IsDBNull(sqlRr["ThoiGianBatDau"])) m.ThoiGianBatDau = Convert.ToDateTime(sqlRr["ThoiGianBatDau"]);
                    if (!Convert.IsDBNull(sqlRr["ThoiGianKetThuc"])) m.ThoiGianKetThuc = Convert.ToDateTime(sqlRr["ThoiGianKetThuc"]);
                    kt = true;
                    lstMonThi.Add(m);
                }
                dp.CloseConnection();
                if (kt) return lstMonThi;
                else return new List<MonThi>();
            }
            else
            {
                return new List<MonThi>();
            }
        }
    }
}
